{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "import datetime\n",
    "from dateutil.relativedelta import relativedelta\n",
    "import pandas as pd\n",
    "from jqdata import *"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "获取ETF份额"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "def get_etf_shares_change():\n",
    "    \"\"\"\n",
    "    获取ETF份额变化率\n",
    "    \"\"\"\n",
    "    # 因为基金数据一般有滞后，可能当天数据还没有，取昨天的\n",
    "       # 因为基金数据一般有滞后，可能当天数据还没有，取昨天的\n",
    "    yesterday = datetime.date.today() - relativedelta(days=1)\n",
    "    yesterday = get_trade_days(end_date=yesterday, count=1)[0]\n",
    "\n",
    "    etf_df = get_all_securities(types=[\"etf\"], date=yesterday)\n",
    "    etf_code_list = list(etf_df.index)\n",
    "\n",
    "    # 1日 1周、1月、3月、半年、1年前日期\n",
    "    last_1_day_date = yesterday -relativedelta(days=1)\n",
    "    last_1_week_date = yesterday - relativedelta(weeks=1)\n",
    "    last_1_month_date = yesterday - relativedelta(months=1)\n",
    "    last_3_month_date = yesterday - relativedelta(months=3)\n",
    "    last_6_month_date = yesterday - relativedelta(months=6)\n",
    "    last_1_year_date = yesterday - relativedelta(years=1)\n",
    "\n",
    "    date_list = [last_1_day_date,last_1_week_date, last_1_month_date, last_3_month_date, last_6_month_date, last_1_year_date]\n",
    "    rate_columns = [\"近1日（%）\",\"近1周（%）\", \"近1月（%）\", \"近3月（%）\", \"近6月（%）\", \"近1年（%）\"]\n",
    "\n",
    "    # 接口限制每次最多返回3000条，不过现在ETF数量就几百只，一次查询就够了\n",
    "    query_filter = query(finance.FUND_SHARE_DAILY).filter(\n",
    "        finance.FUND_SHARE_DAILY.code.in_(etf_code_list),\n",
    "        finance.FUND_SHARE_DAILY.date==yesterday\n",
    "    ).limit(3000)\n",
    "    etf_shares_df = finance.run_query(query_filter)\n",
    "\n",
    "    for i in range(len(date_list)):\n",
    "        # 这一天可能不是交易日, 查他之前最近的交易日，否则查不到份额数据\n",
    "        query_date = get_trade_days(end_date=date_list[i], count=1)[0]\n",
    "        query_filter = query(finance.FUND_SHARE_DAILY).filter(\n",
    "            finance.FUND_SHARE_DAILY.code.in_(etf_code_list),\n",
    "            finance.FUND_SHARE_DAILY.date==query_date\n",
    "        ).limit(3000)\n",
    "        curr_df = finance.run_query(query_filter)[[\"code\", \"shares\"]].copy()\n",
    "        column = \"last_shares_\" + str(i)\n",
    "        curr_df[column] = curr_df[\"shares\"]\n",
    "        etf_shares_df = pd.merge(etf_shares_df, curr_df[[\"code\", column]], on=\"code\", how=\"left\")\n",
    "        etf_shares_df[rate_columns[i]] = round((etf_shares_df[\"shares\"] / etf_shares_df[column] - 1) * 100, 2)\n",
    "\n",
    "    # etf市值，没找到现成的获取etf市值的接口，get_valuation 无法获取基金市值，所以用单位净值*份额=市值\n",
    "    # get_extras拿到的净值和同花顺上看到的差一丢丢，比如2023-04-07 沪深300etf(510300),这里拿到是4.1199，同花顺上是4.117\n",
    "    # 今天取昨天的净值，有的还是没值（比如159605），干脆直接拿一周前的市值（实时性总好过拿基金季报中公布的基金规模）\n",
    "    last_week_trade_date = get_trade_days(end_date=last_1_week_date, count=1)[0]\n",
    "    net_df = get_extras(\"unit_net_value\", etf_code_list, end_date=last_week_trade_date, count=1, df=True).T\n",
    "    net_df[\"code\"] = net_df.index\n",
    "    etf_shares_df = pd.merge(etf_shares_df, net_df, on=\"code\", how=\"left\")\n",
    "\n",
    "    print(net_df.head(20))\n",
    "    # 上面净值df中，净值列名为日期带时分秒（2023-04-07 00:00:00），所以要把date 转为 datetime\n",
    "    net_column = datetime.datetime.combine(last_week_trade_date ,datetime.time())\n",
    "    etf_shares_df[\"市值（亿）\"] = round(etf_shares_df[net_column] * etf_shares_df[\"last_shares_0\"] / 100000000, 2)\n",
    "\n",
    "    # code截取前面的，把后面的字母去掉\n",
    "    etf_shares_df[\"code\"] = etf_shares_df[\"code\"].str.slice(0,6)\n",
    "\n",
    "    eft_shares_change_df = etf_shares_df[[\"code\", \"name\", \"市值（亿）\"] + rate_columns]\n",
    "    return eft_shares_change_df\n"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "ETF分类"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "def etf_classify(etf_change_df):\n",
    "    \"\"\"\n",
    "    ETF归类，只处理指定ETF\n",
    "    \"\"\"\n",
    "    # 类别\n",
    "    type_list = [\"宽基\",\"宽基\",\"宽基\",\"宽基\",\"宽基\",\"宽基\",\"宽基\",\"宽基\",\"宽基\",\"宽基\",\"宽基\",\"宽基\",\"宽基\",\"宽基\",\"宽基\",\"宽基\",\"消费\",\"消费\",\"医药\",\"医药\",\"医药\",\"医药\",\"医药\",\"医药\",\"医药\",\"科技\",\"科技\",\"科技\",\"科技\",\"新能源\",\"新能源\",\"新能源\",\"军工\",\"军工\",\"互联网\",\"互联网\",\"金融地产\",\"金融地产\",\"金融地产\",\"金融地产\",\"红利\"]\n",
    "    # 指数名称\n",
    "    index_list = [\"沪深300\",\"沪深300\",\"沪深300\",\"中证500\",\"中证500\",\"中证1000\",\"中证1000\",\"国证2000\",\"科创50\",\"科创50\",\"创业板指\",\"创业板50\",\"恒生指数\",\"标普500\",\"纳斯达克 100\",\"纳斯达克 100\",\"主要消费\",\"全指可选\",\"全指医药\",\"中证医疗\",\"cs创新药\",\"医疗器械\",\"生物医药\",\"中证中药\",\"恒生医疗\",\"全指信息\",\"半导体\",\"国证芯片\",\"cs计算机\",\"中证新能源\",\"cs新能车\",\"光伏产业\",\"中证军工\",\"军工龙头\",\"恒生科技\",\"恒生科技\",\"中证银行\",\"证券公司\",\"证券公司\",\"房地产\",\"中证红利\"]\n",
    "    # 指数代表基金\n",
    "    name_list = [\"华泰柏瑞沪深300ETF\",\"华夏沪深300ETF\",\"嘉实沪深300ETF\",\"南方中证500ETF\",\"嘉实中证500ETF\",\"南方中证1000ETF\",\"华夏中证1000ETF\",\"万家国证2000ETF\",\"华夏上证科创板50ETF\",\"易方达上证科创板50ETF\",\"易方达创业板ETF\",\"华安创业板50ETF\",\"华夏恒生RTF\",\"国泰标普500ETF\",\"广发纳斯达克100ETF\",\"国泰纳斯达克100ETF\",\"汇添富中证主要消费RTF\",\"广发中证全指可选消费ETF\",\"广发中证全指医药卫生BTF\",\"华宝中证医疗ETF\",\"银华中证创新药产业ETF\",\"永赢中证全指医疗器械ETF\",\"天弘国证生物医药ETF\",\"汇添富中证中药ETF\",\"博时恒生医疗保健ETF\",\"广发中证全指信息技术RTF\",\"国联安中证全指半导体RTF\",\"华夏国证半导体芯片ETF\",\"天弘中证计算机主题ETF\",\"南方中证新能源ETF\",\"华夏中证新能源汽车RTF\",\"华泰柏瑞中证光伏产业ETF\",\"国泰中证军工ETF\",\"富国中证军工龙头ETF\",\"华夏恒生科技ETF\",\"华泰柏瑞南方东英恒生科技 ETF\",\"华宝中证银行ETF\",\"国泰中证全指证券公司ETF\",\"华宝中证全指证券ETF\",\"南方中证全指房地产ETF\",\"易方达中证红利ETF\"]\n",
    "    # 基金代码\n",
    "    code_list = [\"510300\",\"510330\",\"159919\",\"510500\",\"159922\",\"512100\",\"159845\",\"159628\",\"588000\",\"588080\",\"159915\",\"159949\",\"159920\",\"159612\",\"159941\",\"513100\",\"159928\",\"159936\",\"159938\",\"512170\",\"159992\",\"159883\",\"159859\",\"560080\",\"513060\",\"159939\",\"512480\",\"159995\",\"159998\",\"516160\",\"515030\",\"515790\",\"512660\",\"512710\",\"513180\",\"513130\",\"512800\",\"512880\",\"512000\",\"512200\",\"515180\"]\n",
    "\n",
    "    etf_df = pd.DataFrame({\"类别\":type_list, \"指数名称\":index_list, \"指数代表基金\":name_list, \"code\":code_list})\n",
    "    etf_df = pd.merge(etf_df, etf_change_df, on=\"code\", how=\"left\")\n",
    "    etf_df = etf_df.drop(\"name\", axis=1)\n",
    "    return etf_df"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "#全额ETF\n",
    "etf_change_df = get_etf_shares_change()\n",
    "etf_classified_df = etf_classify(etf_change_df)\n",
    "# 输出excel文件到当前研究文件所在目录，可以下载下来\n",
    "etf_classified_df.to_excel(\"ETF份额变化.xlsx\")\n",
    "#全量ETF按周排序输出\n",
    "etf_change_df.sort_values(by=\"近1周（%）\", ascending=False, inplace=True)\n",
    "etf_change_df.to_excel(\"全ETF份额变化.xlsx\")\n",
    "\n",
    "\n",
    "print(etf_classified_df)"
   ],
   "metadata": {
    "collapsed": false
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
